The World Wide Web (WWW) involves a network of servers on the Internet, each of which is associated with one or more Hypertext Markup Language (HTML) pages. The HTML pages are transferred between clients that make requests of servers and the servers using the Hypertext Transfer Protocol (HTTP). Resources available from servers on the Internet are located using a Universal Resource Locator (URL). The standards and protocols of the WWW are promulgated by the World Wide Web Consortium (W3C) through its servers at www.w3c.org, and are used on many private networks in addition to their use on the Internet.
The HTML standard is one application of a more general markup language standard called the Standard Generalized Markup Language (SGML). Recently, a subset of SGML that is more powerful and flexible than HTML has been defined and has gained popularity for transferring information over the Internet and other networks. The new standard, developed and promoted by W3C, is called the eXtensible Markup Language (XML). XML provides a common syntax for expressing structure in data. Structured data refers to data that is tagged for its content, meaning, or use. XML provides an expansion of the tagging that is done in HTML, which focuses on format or presentation. XML tags identify XML elements and attributes of XML elements. XML elements can be nested to form hierarchies of elements.
Given the elements defined and used by XML, a document object model (DOM) is a tree structure formed to define how the information in an XML document is arranged. The DOM is navigated using an XPath expression that indicates a particular node or content in the hierarchy of elements and attributes in an XML document. XPath is a standard promulgated by W3C.
Relational databases predate, and developed independently of, the World Wide Web. Relational databases store data in various types of data containers that correspond to logical relationships within the data. As a consequence, relational databases support powerful search and update capabilities. Relational databases typically store data in tables of rows and columns where the values in all the columns of one row are related. For example, the values in one row of an employee table describe attributes of the same employee, such as her name, social security number, address, salary, telephone number and other information. Each attribute is stored in a different column. Some attributes, called collections, can have multiple entries. For example, the employee may be allowed to have multiple telephone numbers. Special structures are defined in some relational databases to store collections.
A relational database management system (DBMS) is a system that stores and retrieves data in a relational database. The relational DBMS processes requests to perform database functions such as creating and deleting tables, adding and deleting data in tables, and retrieving data from the tables in the database. A well-known standard language for expressing the database requests is the Structured Query Language (SQL).
Because of the popularity of XML as a data exchange format that supports hierarchical relationships among elements, and because of the power of relational DBMSs to update and retrieve data, there is a demand for generating XML data output from relational databases and storing XML data into relational databases. In one approach, a database administrator can commission programming efforts to generate code in a procedural language that maps data in particular XML constructs to data in particular relational database constructs and back. Such programming efforts can be expensive.
In another approach, declarative statements, similar to SQL statements, can be employed to simply express the relationship between XML constructs and SQL constructs. General routines that convert the data according to declared relationships are written one time by a DBMS vendor and supplied to a database administrator. This saves the database administrator from developing procedural language programs to convert the data. To support this demand, an industry standard SQL to operate on XML documents has been developed. This standard is called SQL/XML and information relating to SQL/XML is available at the time of this writing at www.sqlx.org. SQL/XML provides declarative statements that can be used to simply express some conversions between data in hierarchical XML constructs and data in SQL relational constructs (. For example XMLAgg is a SQL/XML function that generates one XML document from a set of XML elements generated from selected rows of a relational table.
As used herein, XML constructs include XML documents, XML elements, document fragments that include multiple XML elements, and XML attributes of XML elements, among others. Data manipulated in an SQL compliant DBMS, and structured by the DBMS so as to support generation of XML constructs to convey that data, are called “instances of XML type,” or simply “XML data.” Such XML data may or may not be stored in such SQL constructs as tables, rows and columns.
While SQL/XML statements provide powerful tools for many circumstances that arise in converting between XML constructs and SQL constructs, they do not simply accommodate all circumstances that arise. For example, an instance of XML type may include data for an employee element that includes several child elements corresponding to various devices signed out to the employee. A user of the DBMS may want to generate a series of XML documents that describes the devices that satisfy some criterion. A conventional SQL/XML statement for extracting those child elements produces a single instance with all the devices that satisfy the criterion. To generate a separate instance of XML type for each separate device, several conventional statements are used, one for each device that satisfies a sub-criterion. It is tedious to generate several statements. In some circumstances it may be impossible to predict the criterion to separate each device from the others. It is preferable in these circumstances to be able to use a single statement to produce the series of separate instances of XML type.
The current SQL/XML statements also do not support retaining some hierarchical relationships when converting between XML constructs and SQL constructs. Some XML elements may be nested within their own type to form a hierarchy. For example, a XML element called “employee” may have one or more other employee elements as child elements, which represent other employees supervised by the first employee. In some circumstances, the SQL/XML function EXTRACT operating on several employee elements in the document will simply output all those several employee element in series, without the nesting indicated in the original XML document. The result is a list of employees without the hierarchical information that indicates whether some employees as supervised children employees of a manager parent employee.
Some SQL rows may imply a hierarchy within a table of rows. For example, an “emp” table may include one row for each employee and include a column “mgr” which holds a pointer to another row of the emp table for a second employee who is a supervisor of the first employee. The SQL/XML function XMLAgg operating on multiple XML elements generated from the rows of this table will simply produce an XML document fragment with a set of root level elements (for example a series of XML elements of element name “employee”). The hierarchy is implied by the mgr attribute of each employee element; but, it would be more desirable if the employee elements were nested so that supervised employees are child elements of the manager employee element.
Based on the foregoing, there is a clear need for declarative statements that enhance the manipulation of XML data in an SQL compliant DBMS. In particular, there is a need for declarative statements that preserve hierarchical relationships implied in SQL constructs when data in those constructs are converted to instances of XML type. There is also a particular need for declarative statements that preserve hierarchical relationships in an instance of XML type when that instance is converted to multiple instances of XML type.
The approaches described in this section could be pursued, but are not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated herein, the approaches described in this section are not to be considered prior art to the claims in this application merely due to the presence of these approaches in this background section.